package cn.cmcc.online.smsapi.sdk.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONObject;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.util.LruCache;
import cn.cmcc.online.smsapi.sdk.constant.Constant;
import cn.cmcc.online.smsapi.sdk.db.bizport.BizportDBManager;
import cn.cmcc.online.smsapi.sdk.log.LogManager;
import cn.cmcc.online.smsapi.sdk.util.StringUtils;

@SuppressLint("NewApi")
public class ParsePhonePubIdManager {
	public static final String TABLE_NAME = "tb_phone_pubid";
	public final static String ID = "id";
	public final static String PHONENUM = "phonenum";
	public final static String PUBLD = "publd";
	public final static String QUERYTIME = "querytime";
	public final static String QUERYFLAG = "queryflag";
	public static final String CREATE_PUB_TABLE = "create table  if not exists "
			+ TABLE_NAME
			+ "("
			+ ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ PHONENUM + " TEXT," + PUBLD + " TEXT," + QUERYFLAG + " TEXT," + QUERYTIME + " number(24))";
	public static final String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
	/*SDK-627 hewengao/20160929 start*/
	public static final String CREATE_INDEX_PHONE="create index if not exists indx_phone on "
	            + TABLE_NAME + " (" + PHONENUM  + ")";
	/*SDK-627 hewengao/20160929 end*/
	public static LruCache<String, String> phonePubIdMap = new LruCache<String, String>(
			200);
	/**
	 * 查询数据库成功后入库缓存
	 * 
	 * */
	public static JSONObject queryPubId(String phoneNum) {
		XyCursor cursor = null;
		JSONObject result = null;
		try {
			cursor = BizportDBManager.query(TABLE_NAME,
					new String[] { PHONENUM, PUBLD, QUERYFLAG, QUERYTIME }, PHONENUM + " =?",
					new String[] { phoneNum });
			if (cursor != null && cursor.moveToNext()) {
				int sindex = cursor.getColumnIndex(PHONENUM);
				int eindex = cursor.getColumnIndex(PUBLD);
				int qindex = cursor.getColumnIndex(QUERYTIME);
				int findex = cursor.getColumnIndex(QUERYFLAG);
				String s = cursor.getString(sindex);
				String e = cursor.getString(eindex);
				long q = cursor.getLong(qindex);
				String f = cursor.getString(findex);
				result = new JSONObject().put("phone", s).put("pubId", e).put("querytime", q).put("queryflag", f);
				return result;
			} else {
				return null;
			}
		} catch (Throwable e) {
			LogManager.e(Constant.TAG, "ParsePhonePubIdManager queryPubId",e);
		} finally {
			XyCursor.closeCursor(cursor, true);
		}
		return null;
	}
	
	   /**
     * 查询数据库成功后入库缓存
     * 
     * */
    public static String querySinglePubId(String phoneNum) {
        XyCursor cursor = null;
        String result = null;
        try {
            cursor = BizportDBManager.query(TABLE_NAME,
                    new String[] {  PUBLD}, PHONENUM + " =?",
                    new String[] { phoneNum });
            if (cursor != null && cursor.moveToFirst()) {
                int eindex = cursor.getColumnIndex(PUBLD);
                result = cursor.getString(eindex);
                return result;
            } else {
                return null;
            }
        } catch (Throwable e) {
            LogManager.e(Constant.TAG, "ParsePhonePubIdManager queryPubId",e);
        } finally {
            XyCursor.closeCursor(cursor, true);
        }
        return null;
    }
    
	/**
	 *根据日期查询pubid为空的结果
	 * 
	 * */
	public static void updateQueryFlag(boolean updateQueryTime) {
	    // 把进行中的数据还原成未进行状态
	    ContentValues values = new ContentValues();
	    values.put(QUERYFLAG, 0);
	    if(updateQueryTime){
	        values.put(QUERYTIME, System.currentTimeMillis());
	    }
	    String whereClause = QUERYFLAG + "=1";
	    
	    try {
            BizportDBManager.update(TABLE_NAME, values, whereClause,null);
        } catch (Exception e) {
            LogManager.e(Constant.TAG, "ResoultMultyUpdate: ", e);
        }
	}
    
    
	
	/**
	 *根据日期查询pubid为空的结果
	 * 
	 * */
	public static JSONArray queryPubIdByDateAndUpdate(long date, String  limit, boolean isPubidNull) {
		JSONArray menuActionArr =  new JSONArray();
		String orderBy = "querytime asc";
		String [] StringTableItem = new String[] {ID, PHONENUM, PUBLD, QUERYFLAG,
				QUERYTIME};
		XyCursor cur = null;
		SQLiteDatabase db = null;
		try {
			db = BizportDBManager.getSQLiteDatabase();
			if (db == null) {
				return null;
			}
			if (isPubidNull){
				cur = BizportDBManager.query(TABLE_NAME,
						StringTableItem, QUERYTIME + " <? and " +  PUBLD + "='' and "+  QUERYFLAG + " =0" ,
						new String[] {  String.valueOf(date)}, null, null, orderBy, limit);
			}else{
				cur = BizportDBManager.query(TABLE_NAME,
						StringTableItem, QUERYTIME + " <? and " +  PUBLD + "!='' and "+  QUERYFLAG + " =0" ,
						new String[] {  String.valueOf(date)}, null, null, orderBy, limit);
			}
			
        	while(cur.moveToNext()){
        		menuActionArr.put(StringUtils.getNoNullString(cur.getString(1)));
        	}
        	//更新数据标记状态
			if (isPubidNull) {
				db.execSQL("UPDATE " + TABLE_NAME + " SET " + QUERYFLAG
						+ " =1 WHERE id IN (SELECT id FROM " + TABLE_NAME
						+ " WHERE " + QUERYTIME + " < " + String.valueOf(date)
						+ " and " + PUBLD + "='' and " + QUERYFLAG
						+ " =0  ORDER BY querytime asc  limit " + limit + " )");
			} else {
				db.execSQL("UPDATE " + TABLE_NAME + " SET " + QUERYFLAG
						+ " =1 WHERE id IN (SELECT id FROM " + TABLE_NAME
						+ " WHERE " + QUERYTIME + " < " + String.valueOf(date)
						+ " and " + PUBLD + "!='' and " + QUERYFLAG
						+ " =0  ORDER BY querytime asc  limit " + limit + " )");
			}
		} catch (Throwable e) {
//			e.printStackTrace();
			LogManager.e(Constant.TAG, "queryPubIdByDateAndUpdate: ", e);
			
		} finally {
			XyCursor.closeCursor(cur, true);
			/* SDK-703 zhaojiangwei 20161216 start */
			BizportDBManager.close(db);
			/* SDK-703 zhaojiangwei 20161216 end */
		}
		return menuActionArr;
	}
	
	
	/**
	 *设置取缓存数据
	 * 
	 * */
	public static void setPubIdByCache(String phoneNum, String result) {
		try {
				phonePubIdMap.put(phoneNum, result);
		} catch (Exception e) {
			LogManager.e(Constant.TAG, "ParsePhonePubIdManager.setPubIdByCache ",e);
		}
	}
	/**
	 * 获取缓存数据
	 * 
	 * */
	public static String getPubIdByCache(String key) {
			return   phonePubIdMap.get(key);
	}
	
	
	/**
	 * 
	 * 单个号码入库
	 */
	public static boolean saveOrUpdateSingleDate(HashMap<String, String> map) {
		if (map == null) {
			return false;
		}

		// TODO Auto-generated method stub
		SQLiteDatabase db = null;
		db = BizportDBManager.getSQLiteDatabase();
		if (db == null) {
			return false;
		}

		try {
			db.beginTransaction();
			long rr = 0;
			ContentValues newValues = new ContentValues();

			newValues.put("PHONENUM", map.get("phone"));
			newValues.put("PUBLD", map.get("pubId"));
			newValues.put("QUERYTIME", map.get("querytime"));
			newValues.put("QUERYFLAG", map.get("queryflag"));
			
			rr = db.update(TABLE_NAME, newValues, PHONENUM + "=?",
					new String[] { map.get("phone") });
			if (rr < 1) {
				rr = db.insert(TABLE_NAME, null, newValues);
			}
		} catch (Throwable e) {
			LogManager.e(Constant.TAG, e.getMessage(), e);
		}finally{
			
			if (db != null) {
				try {
					if (db.inTransaction()) {
						db.setTransactionSuccessful();
						db.endTransaction();
					}
				} catch (Throwable e) {
					LogManager
					.e(Constant.TAG, "ParsePhonePubIdManager.saveOrUpdate "
							+ e.getMessage(), e);
				} finally {
					BizportDBManager.close(db);
				}
			}
		}

		return false;
	}
	
	/**
	 * 批量入库公众号
	 * 
	 * */
	public static boolean saveOrUpdate(final JSONArray jsonArray) {
		if (jsonArray == null) {
			return false;
		}
		// TODO Auto-generated method stub
		SQLiteDatabase db = null;
		db = BizportDBManager.getSQLiteDatabase();
		if (db != null) {
			db.beginTransaction();

			try {
				for (int i = 0; i < jsonArray.length(); i++) {
					JSONObject jb = jsonArray.optJSONObject(i);
					long rr = 0;
					if (jb == null || StringUtils.isNull(jb.optString("phone"))) {
						continue;
					}
					ContentValues newValues = new ContentValues();
					newValues.put("PHONENUM", jb.optString("phone"));
					newValues.put("PUBLD", jb.optString("pubId"));
					newValues.put("QUERYTIME",  System.currentTimeMillis());
					newValues.put("QUERYFLAG",  "0");
					rr = db.update(TABLE_NAME, newValues, PHONENUM + "=?",
							new String[] { jb.optString("phone") });
					if (rr < 1) {
						rr = db.insert(TABLE_NAME, null, newValues);
					}
				}
			} catch (Throwable e) {
				LogManager.e(Constant.TAG, e.getMessage());
			}finally{
				if (db != null) {
					try {
						if (db.inTransaction()) {
							db.setTransactionSuccessful();
							db.endTransaction();
						}
					} catch (Throwable e) {
                        LogManager.w(Constant.TAG, "ParsePhonePubIdManager.saveOrUpdate ", e);
					} finally {
						BizportDBManager.close(db);
					}
				}
			}
		}
		return false;
	}

    /**
     * 批量插入缓存
     * 
     * @param jr
     **/
    public static void insertCatch(JSONArray jr){
    	if(jr==null){
    		return ;
    	}
    	JSONObject jsonItem=new JSONObject();
//    	Object b=cacheMap.get(paramkey);
    	try{
    	for(int i=0;i<jr.length();i++){
    		jsonItem=jr.optJSONObject(i);
    		if(jsonItem!=null){
    			phonePubIdMap.put(jsonItem.optString("phone"), jsonItem.optString("pubId"));
    		}else{
    			continue;
    		}
    	}
    	}catch(Throwable e){
    		LogManager.w(Constant.TAG,"ParsePhonePubIdManager.insertCatch ",e);
    	}
    }
	
	public static List<String> queryAllNumber(boolean isPubIdCanNull){
	    List<String> list = null;
	    XyCursor cursor = null;
	    try{
	        if(isPubIdCanNull){
	            cursor = BizportDBManager.query(TABLE_NAME, new String[]{PHONENUM}, null, null);
	        }else{
	            cursor = BizportDBManager.query(TABLE_NAME, new String[]{PHONENUM}, PUBLD + " !=''", null);
	        }
	        if(cursor!=null){
	            int phoneNumIndex = cursor.getColumnIndex(PHONENUM);
	            list = new ArrayList<String>();
	            while (cursor.moveToNext()) {
                    list.add(cursor.getString(phoneNumIndex));
                }
	        }
	        
	    }catch (Throwable t){
	        LogManager.e(Constant.TAG, "queryAllNumber",t);
	    }finally{
	        XyCursor.closeCursor(cursor, true);
	    }
	    
	    return list;
	}
	 
}
